package com.hll.dao;

import com.hll.model.Book;
import com.hll.model.BookStatus;
import com.hll.model.Pagination;
import common.JDBCAbstractCallBack;
import common.JDBCTemplate;

import java.sql.*;
import java.util.List;

public class BookDao {
    /**调用模块方法*/
    JDBCTemplate<Book> jdbcTemplate = new JDBCTemplate<Book>();

    /**
     * 新增图书
     * */
    public void add(final Book book) {

        String sql = "INSERT INTO book(name, picture, owner_id, owner_name, current_owner_id, current_owner_name, author, description, created_time, updated_time,deleted)"
                + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW(),?)";

        int id = jdbcTemplate.insert(sql, new JDBCAbstractCallBack<Book>() {

            @Override
            public void setParams(PreparedStatement stmt) throws SQLException {
                stmt.setString(1, book.getName());
                stmt.setString(2, book.getPicture());
                stmt.setInt(3, book.getOwnerId());
                stmt.setString(4, book.getOwnerName());
                stmt.setInt(5, book.getCurrentOwnerId());
                stmt.setString(6, book.getCurrentOwnerName());
                stmt.setString(7, book.getAuthor());
                stmt.setString(8, book.getDesc());
                stmt.setInt(9,book.getDeleted());
            }
        });
        book.setId(id);

    }


    /**
     * 更新图书
     */
    public void update(final Book book) {

        String sql = "UPDATE book SET name = ?, author = ?, description = ?, updated_time = NOW() WHERE id = " + book.getId();
        jdbcTemplate.update(sql, new JDBCAbstractCallBack<Book>() {

            @Override
            public void setParams(PreparedStatement stmt) throws SQLException {
                stmt.setString(1, book.getName());
                stmt.setString(2, book.getAuthor());
                stmt.setString(3, book.getDesc());
            }
        });
    }

    /**
     * 整合代码，调用这个方法来使用不同状态图书的的sql
     * */
    private String getWhereSQL (int userId, String status) {
        String sql = "";

        if(status.equals("all")) {
            //全部图书
            sql = " owner_id = " + userId + " AND deleted = 0 ";
        } else if(status.equals("out"))  {
            //借出图书
            sql = " owner_id = " + userId + " AND owner_id != current_owner_id AND deleted = 0 ";
        } else if(status.equals("in"))  {
            //未借出图书
            sql = " owner_id = " + userId + " AND owner_id = current_owner_id AND deleted = 0 ";
        } else if(status.equals("borrow"))  {
            //借入图书
            sql = " current_owner_id = " + userId + " AND owner_id != current_owner_id AND deleted = 0 ";
        }

        return sql;
    }

    /**
     *整合代码，private只允许本类中调用，查询不同状态的图书数据
     */
    public List<Book> query(int userId, BookStatus status, Pagination pagination) {

        //总数据数
        pagination.setTotalCount(this.getMyBookCount(userId, status));

        // 如果当前页大于总页数，则把当前页设置为最大页数
        if (pagination.getCurrentPage() > pagination.getPageCount()) {
            pagination.setCurrentPage(pagination.getPageCount());
        }

        //模块化
        String sql ="SELECT * FROM book AS b WHERE " + this.getWhereSQL(userId, status.getValue()) + " ORDER BY b.id DESC LIMIT " + pagination.getOffset() + "," + pagination.getPageSize();
        List<Book> books = jdbcTemplate.query(sql, new JDBCAbstractCallBack<Book>() {
            @Override
            public Book rsToObject(ResultSet rs) throws SQLException {

                return rsToBook(rs);
            }

        });
        return books;
    }

    /**
     *把这段代码提取成一个方法
     */
    private Book rsToBook(ResultSet rs) throws SQLException {
        Book book = new Book();
        book.setId(rs.getInt("id"));
        book.setAuthor(rs.getString("author"));
        book.setDesc(rs.getString("description"));
        book.setName(rs.getString("name"));
        book.setPicture(rs.getString("picture"));
        book.setOwnerId(rs.getInt("owner_id"));
        book.setCurrentOwnerId(rs.getInt("current_owner_id"));
        book.setCreatedTime(rs.getDate("created_time"));
        book.setUpdatedTime(rs.getDate("updated_time"));
        book.setCurrentOwnerName(rs.getString("current_owner_name"));
        return book;
    }

    /**
     * 查询所有不同状态的图书数量
     */
    public int getMyBookCount(int userId, BookStatus status) {

        String sql ="SELECT count(*) AS myBookCount FROM book WHERE " + this.getWhereSQL(userId, status.getValue()) ;
        return jdbcTemplate.getCount(sql, new JDBCAbstractCallBack<Book>() {

        });
    }


    public Book getById(int id) {

        String sql = "SELECT * FROM book AS b WHERE id = " + id ;
        return jdbcTemplate.queryOne(sql, new JDBCAbstractCallBack<Book>() {
            @Override
            public Book rsToObject(ResultSet rs) throws SQLException {

                return rsToBook(rs);
            }

        });
    }

    /**
     * 逻辑删除
     */
    public void updateDeleted(int id, int deleted) {

        String sql = "UPDATE book SET deleted = " + deleted + ", updated_time = NOW() WHERE id = " + id;
        jdbcTemplate.update(sql, new JDBCAbstractCallBack<Book>() {

        });
    }
}
